package com.tokenmaster.aitrade.data;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.Queue;

import org.apache.log4j.Logger;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.tokenmaster.aitrade.common.ticker.TickerData;
import com.tokenmaster.aitrade.common.ticker.Ticker_Spot;

public class SystemDB_TickersHistory {

	private static SystemDB_TickersHistory instance = new SystemDB_TickersHistory();

	public static SystemDB_TickersHistory getInstance() {
		return instance;
	}

	private static Logger logger = Logger.getLogger(SystemDB_TickersHistory.class);

	private ComboPooledDataSource cpds = new ComboPooledDataSource();

	private final String TICKERHISTORY_TABLE = "tickers_history";

	public SystemDB_TickersHistory() {
		// 从配置文件中读取数据库连接信息
		Properties propfile = new Properties();
		try {
			InputStream inputStream = new BufferedInputStream(new FileInputStream("ticker_db.properties"));
			propfile.load(inputStream);

			String name = propfile.getProperty("name");
			String url = propfile.getProperty("url");
			String user = propfile.getProperty("user");
			String pass = propfile.getProperty("pass");

			inputStream.close();

			// logger.info("DBUrl = " + url);

			cpds.setDriverClass(name);
			cpds.setJdbcUrl(url);
			cpds.setUser(user);
			cpds.setPassword(pass);
			cpds.setMaxStatements(180);
			cpds.setMaxPoolSize(100);

		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public int getTickersList(Queue<Ticker_Spot> ticker_Spots, int unixTime) {

		int result = -1;

		String sql = "select * from " + TICKERHISTORY_TABLE + " where time>? order by time limit 800";

		try {
			java.sql.Connection conn = cpds.getConnection();
			java.sql.PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, unixTime);
			ResultSet rs = pstmt.executeQuery();

			int i = 0;
			while (rs.next()) {

				if (i % 10 == 0) {
					Ticker_Spot ticker_Spot = new Ticker_Spot();
					ticker_Spot.setSymbol("btc_cny");
					ticker_Spot.setLast(String.valueOf(rs.getDouble("last")));
					ticker_Spot.setVol(String.valueOf(rs.getDouble("vol")));
					ticker_Spot.setDate(String.valueOf(rs.getLong("time") * 1000));
					result = rs.getInt("time");

					ticker_Spots.offer(ticker_Spot);
				}
				i++;
			}
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return result;
	}
	
	public int getTickerDatasList(Queue<TickerData> tickerDatas, int unixTime) {

		int result = -1;

		String sql = "select * from " + TICKERHISTORY_TABLE + " where time>? order by time limit 800";

		try {
			java.sql.Connection conn = cpds.getConnection();
			java.sql.PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, unixTime);
			ResultSet rs = pstmt.executeQuery();

			int i = 0;
			while (rs.next()) {

				if (i % 10 == 0) {
					TickerData tickerData = new TickerData();
		
					tickerData.setLast(rs.getDouble("last"));
					tickerData.setVol(rs.getDouble("vol"));
					tickerData.setUnixtime(rs.getLong("time") * 1000);
					result = rs.getInt("time");

					tickerDatas.offer(tickerData);
				}
				i++;
			}
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return result;
	}

}
